﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;

namespace You2V.LocalDBLayer
{
    public class DBAccess
    {
        //mdb路径, todo, changed to related path
        //private static string strMdb = "D:\\GangCheng\\Personal\\you2v\\OurCodes2012\\you2v\\You2V.mdb";

        //mdb path, together with exe. todo
        private static string strMdb = "You2V.mdb";
        private static string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMdb + "";

        private static bool traceDetail = false;
        static int QueryCounter = 0;
        static int CommandCounter = 0;

        public DBAccess()
        {
        }

        public static DataTable GetDataTable(string sqlQuery)
        {
            if (traceDetail)
            {
                Debug.WriteLine("start GetDataTable(" + QueryCounter + "), query:\r\n" + sqlQuery);
                QueryCounter++;
            }

            OleDbConnection Connection = null;
            try
            {
                Connection = new OleDbConnection(ConnectionString);
                Connection.Open();

                DataTable dt = new DataTable();
                OleDbDataAdapter da = new OleDbDataAdapter(sqlQuery, Connection);
                da.Fill(dt);

                if (traceDetail)
                {
                    Debug.WriteLine("end GetDataTable(" + QueryCounter+")");
                }

                return dt;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }

            if (traceDetail)
            {
                Debug.WriteLine("end GetDataTable(" + QueryCounter + ")");
            }

            return null;
        }
        //public static int GetDBIntValue(object dbObject)
        //{
        //    if (dbObject == System.DBNull)
        //        return 0;
        //    else
        //        return (int)dbObject;
        //}
        //public static string GetDBStringValue(object dbObject)
        //{
        //    if (dbObject == System.DBNull)
        //        return "";
        //    else
        //        return (string)dbObject;
        //}

        public static bool RunCommand(string sqlCommand)
        {
            if (traceDetail)
            {
                Debug.WriteLine("start RunCommand(" + CommandCounter + "), sqlCommand:\r\n" + sqlCommand);
                QueryCounter++;
            }
            OleDbConnection Connection = null;
            try
            {
                Connection = new OleDbConnection(ConnectionString);
                Connection.Open();

                OleDbCommand command = null;

                command = Connection.CreateCommand();
                command.CommandTimeout = 10;

                command.CommandText = sqlCommand;

                int count = command.ExecuteNonQuery();
                Connection.Close();
                Connection = null;

                command = null;

                if (traceDetail)
                {
                    Debug.WriteLine("end RunCommand(" + CommandCounter + ")");
                }

                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }

            if (traceDetail)
            {
                Debug.WriteLine("end RunCommand(" + CommandCounter + ")");
            }

            return false;
        }
    }
}
